import pymysql
from CourseAssistant.constant import ip, user, password, dbname

config = {
    'host': ip
    , 'user': user
    , 'password': password
    , 'database': dbname
    , 'charset': 'utf8'
    , 'port': 3306
}


#
# class SqlOdbc:
#     db = None
#     cursor = None
#
#     def __init__(self, dbname, ip="localhost", username="root", password=""):
#         # self.db = pymysql.connect(ip, username, password, dbname, charset='utf8mb4')
#         self.db = pymysql.connect(**config)
#         self.cursor = self.db.cursor()
#
def sqlexecute(type, tablename, values=None, where: dict = None, whereand=True):
    types = {
        's': select,
        'i': insert,
        'u': update,
        'd': delete
    }
    sql, mid = types[type](tablename, values)
    if where:
        temp = " and " if whereand else " or"
        wheresql = ""
        for key in where.keys():
            if wheresql != "":
                wheresql += temp
            wheresql = wheresql + key + '= ' + mystr(where[key])
        sql = sql + " where " + wheresql
    if type == 's':
        effect_row = cursor.execute(sql)
        data = cursor.fetchall()
        return data, effect_row
    else:
        try:
            effect_row = cursor.execute(sql)
            db.commit()
            return effect_row, mid
        except:
            db.rollback()
            return 0


def select(tablename, fileds=None):
    if fileds is None:
        fileds = ["*"]
    sql = "select " + ",".join(fileds) + " from " + tablename
    return sql, -1


def insert(tablename, values):
    idsql = "select max(id) from " + tablename
    cursor.execute(idsql)
    maxid = (cursor.fetchall())[0][0]
    if maxid is None:
        maxid = 0
    mid = maxid + 1
    sql = "insert into " + tablename + " values " + "(" + str(mid) + str(values).replace("[", ",").replace("]",
                                                                                                                 ")")
    return sql, mid


def delete(tablename, values):
    sql = "delete from " + tablename
    return sql, -1


def update(tablename, oldvalue: dict):
    upsql = ""
    for key in oldvalue:
        if upsql != "":
            upsql += ","
        upsql = upsql + key + ' = ' + mystr(oldvalue[key])
    sql = "update " + tablename + " set " + upsql
    return sql, -1


def create():
    db = pymysql.connect(host=ip, user=user, password=password, charset='utf8mb4')
    cursor = db.cursor()
    sql = 'create database if not exists ' + dbname
    cursor.execute(sql)

    db = pymysql.connect(**config)
    cursor = db.cursor()
    sqlteacher = '''
        create table if not exists `teacher` (
            `id` INT NOT NULL AUTO_INCREMENT,
            `user_name` VARCHAR(20),
            `user_id` VARCHAR(100) NOT NULL,
            `password` VARCHAR(100) NOT null,
            PRIMARY KEY (`id`)
        ) ENGINE = InnoDB DEFAULT CHARSET=utf8;
    '''

    sqlcourse = '''
        create table if not exists `course` (
            `id` int not null AUTO_INCREMENT,
            `course_name` varchar(100),
            `teacher_id` int not null ,
            `mid_weight` int,
            `final_weight` int,
            `homework_weight` int,
            `absent_score` int,
            `collage_id` int,
            PRIMARY KEY (`id`)
        )  ENGINE = InnoDB DEFAULT CHARSET=utf8;        
    '''

    sql_student_course = '''                                    
        create table if not exists `student_course` (                        
            `id` int not null AUTO_INCREMENT,  
            `student_id` varchar(100),       
            `student_name` varchar(100),                
            `course_id` int not null ,                
            `mid_score` varchar(100),                          
            `final_score` varchar(100),                        
            `homework_score` varchar(100),                     
            `absent_num` varchar(100),                        
            `score` varchar(100),                          
            PRIMARY KEY (`id`)                         
        )  ENGINE = InnoDB DEFAULT CHARSET=utf8;       
    '''

    sqlcollage = '''                     
        create table if not exists `collage` (          
            `id` int not null AUTO_INCREMENT,                   
            `collage_name` varchar(100),                                     
            PRIMARY KEY (`id`)                   
        )  ENGINE = InnoDB DEFAULT CHARSET=utf8; 
    '''

    cursor.execute(sqlteacher)
    cursor.execute(sqlcourse)
    cursor.execute(sql_student_course)
    cursor.execute(sqlcollage)


def mystr(value):
    if isinstance(value, str):
        return '"' + value + '"'
    return str(value)


create()
db = pymysql.connect(**config)
cursor = db.cursor()
